package cn.hpclub.server.controller.admin;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringEscapeUtils;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.jfinal.aop.Before;
import com.jfinal.core.Controller;
import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;

import cn.hpclub.server.bean.ClientJsonObject;
import cn.hpclub.server.constant.ClientApiConstant;
import cn.hpclub.server.interceptor.AdminInterceptor;


@Before(AdminInterceptor.class)
public class SqlController extends Controller{

    private static final String URL_SQL          = "/admin/sqlbrowser.tpl";

    public void sqlbrowser(){
        render(URL_SQL);
    }

    public void showTableConstruct(){// 表结构
        // show columns from sys_config
        // describe sys_config
        String tableName = getPara("tableName");
        String sql = "show columns from " + tableName;
        List<Record> records = Db.find(sql);
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        for(Record record : records){
            list.add(record.getColumns());
        }

        ClientJsonObject cjo = new ClientJsonObject();
        cjo.resultCode = ClientApiConstant.ResultCode.SUCCESS_CODE;
        cjo.message = ClientApiConstant.Msg.SUCCESS;
        JSONObject object = new JSONObject();
        object.put("result", 0);// 表示查询语句
        object.put("resultDesp", "查询语句，共 " + list.size() + " 条记录");// 信息
        object.put("sqlStatement", sql);// 返回sql语句
        object.put("datamap", list);// 把数据输出

        cjo.obj = object;
        renderJson(JSON.toJSONString(cjo, true));
    }
    
    /**
     * 
     * @Title: tables
     * @Description:获取数据库中的表
     */
    public void tables(){
        String tableName = getPara("tableName", "").trim();
        List<Record> records = Db.find("show tables like ? ;", "%" + tableName + "%");
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        for(Record record : records){
            list.add(record.getColumns());
        }
        ClientJsonObject cjo = new ClientJsonObject();
        cjo.resultCode = ClientApiConstant.ResultCode.SUCCESS_CODE;
        cjo.message = ClientApiConstant.Msg.SUCCESS;
        cjo.obj = list;
        renderJson(JSON.toJSONString(cjo, true));
    }

    /**
     * 
     * @Title: execute
     * @Description:执行SQL语句并返回结果
     */
    public void execute(){
        String sql = getPara("sql-statement").trim();
        if(StrKit.isBlank(sql)){
            throw new RuntimeException("sql语句不对");
        }
        // sql可能是被转义的，所以反转义回来
        sql = StringEscapeUtils.unescapeHtml(sql);
        ClientJsonObject cjo = new ClientJsonObject();
        cjo.resultCode = ClientApiConstant.ResultCode.SUCCESS_CODE;
        cjo.message = ClientApiConstant.Msg.SUCCESS;
        List<Record> records = Db.find(sql);

        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        for(Record record : records){
            list.add(record.getColumns());
        }

        JSONObject object = new JSONObject();
        object.put("result", 0);// 表示select语句
        object.put("resultDesp", "select语句，共 " + list.size() + " 条记录");// 信息
        object.put("sqlStatement", sql);// 返回sql语句
        object.put("datamap", list);// 把数据输出

        cjo.obj = object;
        renderJson(JSON.toJSONString(cjo, true));
    }
}
